import random
import traceback

import pymysql
from pymysql.cursors import Cursor

import mysql_connect

global already
global text_id

class UserLikeCount:
    def __init__(self, id, essay_count=None, answer_count=None, question_count=None):
        self.id = id
        self.essay_count = essay_count
        self.answer_count = answer_count
        self.question_count = question_count


class LikeRel():
    def __init__(self):
        self.id = None  # 主键
        self.text_id = None  # 回答ID
        self.user_id = None  # 用户ID


class Already():
    answer: list[int] = []
    essay: list[int] = []
    question: list[int] = []

    def __init__(self):
        self.answer: list[int] = []
        self.essay: list[int] = []
        self.question: list[int] = []


class TextId():
    answer: list[int]
    essay: list[int]
    question: list[int]

def get_Text_Id():
    conn,cursor = mysql_connect.get_conn()
    text_id = TextId()
    try:
        sql = """
            select id from shane_essay;
        """
        cursor.execute(sql)
        essay_result = cursor.fetchall()
        text_id.essay = [ id[0] for id in essay_result ]

        sql = """
                    select id from shane_answer;
                """
        cursor.execute(sql)
        answer_result = cursor.fetchall()
        text_id.answer = [id[0] for id in answer_result]

        sql = """
                            select id from shane_question;
                        """
        cursor.execute(sql)
        question_result = cursor.fetchall()
        text_id.question = [id[0] for id in question_result]

        return text_id

    except:
        print("大失败")
        traceback.print_exc()
    finally:
        cursor.close()
        conn.close()


def get_user_like_counts() -> list[UserLikeCount]:
    conn, cursor = mysql_connect.get_conn()
    like_list: list[UserLikeCount] = []
    try:
        sql = """
            select id,essay_count,answer_count,question_count from user_like_count;
        """
        cursor.execute(sql)
        result = cursor.fetchall()
        like_list = [UserLikeCount(like[0], like[1], like[2], like[3]) for like in result]
    except:
        traceback.print_exc()
    finally:
        cursor.close()
        conn.close()

    return like_list


def get_already() -> Already:
    conn, cursor = mysql_connect.get_conn()
    already = Already()
    try:
        sql = """
                select
                    DISTINCT user_id
                FROM
	                shane_likes_essay;
            """
        cursor.execute(sql)
        essay_result = cursor.fetchall()
        already.essay = [user_id[0] for user_id in essay_result]
        sql = """
                        select
                            DISTINCT user_id
                        FROM
        	                shane_likes_answer;
                    """
        cursor.execute(sql)
        answer_result = cursor.fetchall()
        already.answer = [user_id[0] for user_id in answer_result]
        sql = """
                                select
                                    DISTINCT user_id
                                FROM
                	                shane_likes_question;
                            """
        cursor.execute(sql)
        question_result = cursor.fetchall()
        already.question = [user_id[0] for user_id in question_result]
    except:
        traceback.print_exc()
    finally:
        cursor.close()
        conn.close()

    return already


def make_data(like: UserLikeCount, conn: pymysql.Connection, cursor: Cursor):
    if like.id not in already.essay:
        insert_one_essay(like,conn,cursor)
    else:
        print("essay已存在，跳过")
    if like.id not in already.answer:
        insert_one_answer(like,conn,cursor)
    else:
        print("answer已存在，跳过")
    if like.id not in already.question:
        insert_one_question(like, conn, cursor)
    else:
        print("qestion已存在，跳过")

    try:
        conn.commit()
    except:
        print("大失败")
        traceback.print_exc()



def insert_one_essay(like: UserLikeCount, conn: pymysql.Connection, cursor: Cursor):
    try:
        sql = f"""
            insert into 
                shane_likes_essay
                    (id, essay_id, user_id)
                values
                    (snowflake_sequence(),%s,%s) 
        """
        global text_id
        insert_list = []
        essay_id_list = random.sample(text_id.essay, like.essay_count)
        for essay_id in essay_id_list:
            one_list = [essay_id, like.id]
            insert_list.append(one_list)
        # print(insert_list)
        cursor.executemany(sql,insert_list)
    except:
        print("大失败")
        traceback.print_exc()

def insert_one_answer(like: UserLikeCount, conn: pymysql.Connection, cursor: Cursor):
    try:
        sql = f"""
            insert into 
                shane_likes_answer
                    (id, answer_id, user_id)
                values
                    (snowflake_sequence(),%s,%s) 
        """
        global text_id
        insert_list = []
        answer_id_list = random.sample(text_id.answer, like.answer_count)
        for answer_id in answer_id_list:
            one_list = [answer_id, like.id]
            insert_list.append(one_list)
        # print(insert_list)
        cursor.executemany(sql,insert_list)
    except:
        print("大失败")
        traceback.print_exc()

def insert_one_question(like: UserLikeCount, conn: pymysql.Connection, cursor: Cursor):
    try:
        sql = f"""
            insert into 
                shane_likes_question
                    (id, question_id, user_id)
                values
                    (snowflake_sequence(),%s,%s) 
        """
        global text_id
        insert_list = []
        question_id_list = random.sample(text_id.question, like.question_count)
        for question_id in question_id_list:
            one_list = [question_id, like.id]
            insert_list.append(one_list)
        # print(insert_list)
        cursor.executemany(sql,insert_list)
    except:
        print("大失败")
        traceback.print_exc()

def main():
    like_list = get_user_like_counts()
    global already
    already = get_already()
    global text_id
    text_id = get_Text_Id()
    conn, cursor = mysql_connect.get_conn()
    try:
        for index, like in enumerate(like_list):
            print(f"现在是第{index}个，总共{len(like_list)}个")
            make_data(like, conn, cursor)
    except:
        print("巨大失败")
        traceback.print_exc()
    finally:
        cursor.close()
        conn.close()



if __name__ == "__main__":
    main()
